package App.Repository;

import App.Model.ConnectinManager;
import App.Model.ConnectionInfo;
import App.Model.Task.CrmTaskSchedule;
import App.Model.Task.TaskJobInfo;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * Created by xw on 2019/1/31.
 */
public class JobRepository {

    private final Logger logger = LoggerFactory.getLogger(this.getClass());


    public Connection getConnection(String url, String username, String password) {
        try {
            return DriverManager.getConnection(url, username, password);
        } catch (SQLException ex) {
            return null;
            // throw ex;
        }
    }


    public TaskJobInfo getJobData(String reminderid, String _taskid) throws Exception {

        ConnectionInfo _connection = ConnectinManager.getConnectionInfo();
        String _url = _connection.getUrl();
        String _username = _connection.getUsername();
        String _password = _connection.getPassword();

        TaskJobInfo _jobInfo = new TaskJobInfo();

        List<CrmTaskSchedule> _scheduleList = new ArrayList<>();
        Connection conn = null;
        try {


            conn = getConnection(_url, _username, _password);
            String _strSqlgetReminder = " select recid,taskid,repeattype,reminder_minute,aheadtype,reccreator " +
                    " from crm_task_reminder " +
                    " where recid=? ";




            PreparedStatement _preStatementFirst = conn.prepareStatement(_strSqlgetReminder);
            _preStatementFirst.setString(1, reminderid);
            ResultSet _resultFirst = _preStatementFirst.executeQuery();
            while (_resultFirst.next()) {
                //_taskid = _resultFirst.getString("taskid");
            }


            String _strSql = " select m.userid from crm_task_member m " +
                    " where m.taskid=? " +
                    " and m.userid not in (select reccreator from crm_task_reminder where taskid=? ) " +
                    " and m.userid>0 " +
                    " and m.recstatus=1 " +
                    " union (select reccreator from  crm_task_reminder where recid=? ) ";

            PreparedStatement _preStatement = conn.prepareStatement(_strSql);
            _preStatement.setString(1, _taskid);
            _preStatement.setString(2, _taskid);
            _preStatement.setString(3, reminderid);
            ResultSet rs = _preStatement.executeQuery();
            List<Integer> _userIds = new ArrayList<>();
            while (rs.next()) {
                int _userid = rs.getInt("userid");
                _userIds.add(_userid);
            }

            logger.info("step 5");


            String _taskContent = "";
            Date _taskBegindate = null;
            String _addressInfo = "";
            String _strSqlgetTask = " select content,begintime,address_name from crm_task where recid=? ";
            PreparedStatement _preStatementSecond = conn.prepareStatement(_strSqlgetTask);
            _preStatementSecond.setString(1, _taskid);
            ResultSet _resultTask = _preStatementSecond.executeQuery();
            while (_resultTask.next()) {
                _taskContent = _resultTask.getString("content");
                _taskBegindate = _resultTask.getDate("begintime");
                _addressInfo = _resultTask.getString("address_name");
            }


            String _projectid = "";
            String _projectName = "";
            int _stageid = 0;
            String _stageName = "";
            String _strSqlgetProjectInfo = "  select pt.projectid,p.recname,pt.stageid,s.recname" +
                    " from crm_project_task pt " +
                    " inner join crm_project p on p.recid=pt.projectid" +
                    " inner join crm_project_stage s on pt.stageid=s.recid" +
                    " where pt.recstatus=1   " +
                    " and  pt.taskid=? ";

            PreparedStatement _preStatementProject = conn.prepareStatement(_strSqlgetProjectInfo);
            _preStatementProject.setString(1, _taskid);
            ResultSet _resultProject = _preStatementProject.executeQuery();
            while (_resultProject.next()) {
                _projectid = _resultProject.getString("projectid");
                _projectName = _resultProject.getString("recname");
                _stageid = _resultProject.getInt("stageid");
                _stageName = _resultProject.getString("recname");
            }


            logger.info("the begin time:" + _taskBegindate);

            SimpleDateFormat _dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            _jobInfo.setMembers(_userIds);
            _jobInfo.setTaskid(_taskid);
            _jobInfo.setTaskcontent(_taskContent);

            _jobInfo.setTaskbegintime(_dateFormat.format(_taskBegindate));
            //_jobInfo.setTaskbegintime("2019-03-18 09:16:13");
            _jobInfo.setProjectid(_projectid);
            _jobInfo.setProjectname(_projectName);
            _jobInfo.setStageid(_stageid);
            _jobInfo.setStagename(_stageName);
            _jobInfo.setAddress(_addressInfo);

            rs.close();
            conn.close();

        } catch (Exception ex) {
            ex.printStackTrace();
            logger.info("this is the error:" + ex.getMessage());
        } finally {
            conn.close();
        }
        return _jobInfo;
    }

    //end of class
}
